# database.py
import sqlite3


def create_database():
    """创建数据库和表结构"""
    conn = sqlite3.connect('taobao_dashboard.db')
    cursor = conn.cursor()

    # 店铺基本信息表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS shops (
        shop_id INTEGER PRIMARY KEY,
        shop_name TEXT,
        category TEXT,
        create_date DATE
    )''')

    # 流量总览表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS traffic_overview (
        record_id INTEGER PRIMARY KEY AUTOINCREMENT,
        shop_id INTEGER,
        date DATE,
        total_visitors INTEGER,
        total_pageviews INTEGER,
        bounce_rate FLOAT,
        conversion_rate FLOAT,
        payment_count INTEGER,
        payment_amount FLOAT,
        FOREIGN KEY (shop_id) REFERENCES shops(shop_id)
    )''')

    # 流量来源表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS traffic_sources (
        source_id INTEGER PRIMARY KEY AUTOINCREMENT,
        shop_id INTEGER,
        date DATE,
        source_type TEXT,
        source_name TEXT,
        visitors INTEGER,
        conversion_rate FLOAT,
        rank INTEGER,
        FOREIGN KEY (shop_id) REFERENCES shops(shop_id)
    )''')

    # 关键词表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS keywords (
        keyword_id INTEGER PRIMARY KEY AUTOINCREMENT,
        shop_id INTEGER,
        date DATE,
        keyword TEXT,
        visitors INTEGER,
        rank INTEGER,
        FOREIGN KEY (shop_id) REFERENCES shops(shop_id)
    )''')

    # 商品流量表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS product_traffic (
        product_id INTEGER PRIMARY KEY AUTOINCREMENT,
        shop_id INTEGER,
        date DATE,
        product_name TEXT,
        visitors INTEGER,
        conversion_rate FLOAT,
        rank INTEGER,
        FOREIGN KEY (shop_id) REFERENCES shops(shop_id)
    )''')

    # 人群特征表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS user_demographics (
        demo_id INTEGER PRIMARY KEY AUTOINCREMENT,
        shop_id INTEGER,
        date DATE,
        crowd_type TEXT,
        gender TEXT,
        age_group TEXT,
        city TEXT,
        taoqi_value TEXT,
        proportion FLOAT,
        FOREIGN KEY (shop_id) REFERENCES shops(shop_id)
    )''')

    conn.commit()
    return conn